package cn.com.libertymutual.sys.dao;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import cn.com.libertymutual.core.web.ServiceResult;
import cn.com.libertymutual.sys.bean.SysCodeNode;

public interface ISysCodeNodeDao extends PagingAndSortingRepository<SysCodeNode, Integer>, JpaSpecificationExecutor<SysCodeNode> {
	public List<SysCodeNode> findByCodeType(String codeType);

	// @Query(value="delete from qa_concern_user where user_id=?1 and concern_user_id=?2)",nativeQuery=true)

	/*
	 * @Modifying
	 * 
	 * @Query(
	 * value="select node from  SysCodeNode node where node.codeType=?1 and node.riskCode=?2 "
	 * ,nativeQuery=true)
	 * 
	 * public Iterable<SysCodeNode> findCodeInfoByRiskCode(String codeType, String
	 * riskCode) ;
	 * 
	 * //from User u where u.username = ?1
	 * 
	 * @Query("select node from SysCodeNode node where node.codeType = :#{#node.codeType} and node.riskCode = :#{#node.riskCode}"
	 * ) public Iterable<SysCodeNode> findByCodeTypeAndRiskCode(SysCodeNode node);
	 */
	// @Modifying
	@Query(value = "select  new cn.com.libertymutual.sys.bean.SysCodeNode(n.code,n.codeEname,n.codeCname)  from SysCodeNode n,SysCodeRisk r  where r.codeType = n.codeType  and r.code = n.code  and r.codeType =?1 and r.riskCode =?2     and n.validStatus =?3  order by n.serialNo asc ", nativeQuery = true)

	public Iterable<SysCodeNode> findCodeInfoByRiskCode(String codeType, String riskCode, String validStatus);

	@Query(" from SysCodeNode  where codeType = ?1  and branchCode= ?2")
	public List<SysCodeNode> findByCodeBranch(String codeType, String branchCode);

	@Modifying
	@Query(" from SysCodeNode  where codeType = ?1  and code like ?2")
	public Iterable<SysCodeNode> findByCodeTypeAndCodeLike(String codeType, String code);

	@Query(" from SysCodeNode  where codeType =?1  and validStatus =?2 order by  serialNo asc")
	public Iterable<SysCodeNode> findByCodeTypeAndvalidStatusOrderBySerialNoAsc(String codeType, String code);

	public Page<SysCodeNode> findByCodeType(String codeType, Pageable pageable);

	@Query(value=" select count(*) from tb_sys_code_node where code_type = 'ProductCompany'", nativeQuery = true)
	public Integer findProductCompanyCount();
	
	@Query(value=" select * from tb_sys_code_node where code_type = 'ProductCompany' order by id asc limit ?1 , ?2", nativeQuery = true)
	public List<SysCodeNode> findProductCompany(int pageNumber, int pageSize);

}